package com.softer.chengxin.excel.dao.provider;

import com.softer.chengxin.common.domin.Common;
import com.softer.chengxin.common.enums.InfoTypeEnum;
import com.softer.chengxin.common.enums.LevelEnum;
import com.softer.chengxin.common.enums.StatisticalReportEnum;
import com.softer.chengxin.excel.domin.ExportExcel;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author Gong Zhiyang
 * @date 2019/2/25
 */
public class ReportDaoProvider1 {
    public String getReport(HashMap<String, Object> map) {
        String fields = (String) map.get("fields");
        String area = (String) map.get("area");
        Integer roleId = (Integer) map.get("roleId");
        String type;
        if (Common.THREE.equals(roleId)) {
            type = "district";
        } else {
            type = "street";
        }
        StringBuffer sql = new StringBuffer(20);
        sql.append("SELECT ds.name ");
        //1查询企业数量
        if (fields.contains(StatisticalReportEnum.CORP_NUM.getField())) {
            sql.append(",IFNULL(b.corpNum,0) as corpNum");
        }
        //2项目数量
        if (fields.contains(StatisticalReportEnum.PROJECT_NUM.getField())) {
            sql.append(",IFNULL(c.projectNum,0) as projectNum");
        }
        //3通过三体系认证数量
        if (fields.contains(StatisticalReportEnum.THREE_SYSTEM.getField())) {
            sql.append(",IFNULL(d.three_system,0) as three_system");
        }
        //4未通过三体系认证数量
        if (fields.contains(StatisticalReportEnum.NO_THREE_SYSTEM.getField())) {
            sql.append(",IFNULL(e.no_three_system,0) as no_three_system");
        }
        //5成立党支部数量
        if (fields.contains(StatisticalReportEnum.ORGANIZATION.getField())) {
            sql.append(",IFNULL(f.organization,0) as organization");
        }
        //6未成立党支部数量
        if (fields.contains(StatisticalReportEnum.NO_ORGANIZATION.getField())) {
            sql.append(",IFNULL(g.no_organization,0) as no_organization");
        }
        //7主营收入
        if (fields.contains(StatisticalReportEnum.MAIN_INCOME.getField())) {
            sql.append(",IFNULL(h.main_business_income,0) as main_business_income");
        }
        //8公共收益
        if (fields.contains(StatisticalReportEnum.PUBLIC_INCOME.getField())) {
            sql.append(",IFNULL(j.public_income,0) as public_income");
        }
        //9利润
        if (fields.contains(StatisticalReportEnum.PROFIT.getField())) {
            sql.append(",IFNULL(k.profit,0) as profit");
        }
        //10其他收入
        if (fields.contains(StatisticalReportEnum.OTHER_INCOME.getField())) {
            sql.append(",IFNULL(l.other_income,0) as other_income");
        }
        //11企业总人数
        if (fields.contains(StatisticalReportEnum.CORP_ALL_PERSON_NUM.getField())) {
            sql.append(",IFNULL(m.corp_all_person_num,0) as corp_all_person_num");
        }
        //12企业交五险一金人数
        if (fields.contains(StatisticalReportEnum.CORP_INSURANCE.getField())) {
            sql.append(",IFNULL(n.corp_insurance,0) as corp_insurance");
        }
        //13企业持有相关中等从业资格证书的人数
        if (fields.contains(StatisticalReportEnum.CORP_CERTIFICATE.getField())) {
            sql.append(",IFNULL(p.corp_certificate,0) as corp_certificate");
        }
        //14项目总人数
        if (fields.contains(StatisticalReportEnum.PROJECT_ALL_PERSON_NUM.getField())) {
            sql.append(",IFNULL(q.project_all_person_num,0) as project_all_person_num");
        }
        //15项目交五险一金人数
        if (fields.contains(StatisticalReportEnum.PROJECT_INSURANCE.getField())) {
            sql.append(",IFNULL(r.project_insurance,0) as project_insurance");
        }
        //16项目持有相关中等从业资格证书的人数
        if (fields.contains(StatisticalReportEnum.PROJECT_CERTIFICATE.getField())) {
            sql.append(",IFNULL(s.project_certificate,0) as project_certificate");
        }
        //17 D级
        if (fields.contains(StatisticalReportEnum.LEVEL_D.getField())) {
            sql.append(",IFNULL(t.level_D,0) as level_D");
        }
        //18 A级
        if (fields.contains(StatisticalReportEnum.LEVEL_A.getField())) {
            sql.append(",IFNULL(u.level_A,0) as level_A");
        }
        //19 AA级
        if (fields.contains(StatisticalReportEnum.LEVEL_AA.getField())) {
            sql.append(",IFNULL(v.level_AA,0) as level_AA");
        }
        //20 AAA级
        if (fields.contains(StatisticalReportEnum.LEVEL_AAA.getField())) {
            sql.append(",IFNULL(w.level_AAA,0) as level_AAA");
        }
        //21 黑名单数量
        if (fields.contains(StatisticalReportEnum.BLACK_LIST.getField())) {
            sql.append(",IFNULL(x.black_list,0) as black_list");
        }
        //22 项目总占地面积
        if (fields.contains(StatisticalReportEnum.PROJECT_ALL_AREA.getField())) {
            sql.append(",IFNULL(y.project_all_area,0) as project_all_area");
        }
        //23 地上建筑面积
        if (fields.contains(StatisticalReportEnum.GROUND_AREA.getField())) {
            sql.append(",IFNULL(z.ground_area,0) as ground_area");
        }
        //24 地下建筑面积
        if (fields.contains(StatisticalReportEnum.UNDERGROUND_AREA.getField())) {
            sql.append(",IFNULL(aa.underground_area,0) as underground_area");
        }
        //25 住宅建筑面积
        if (fields.contains(StatisticalReportEnum.RESIDENTIAL_AREA.getField())) {
            sql.append(",IFNULL(bb.residential_area,0) as residential_area");
        }
        //26 非住宅建筑面积
        if (fields.contains(StatisticalReportEnum.NON_RESIDENTIAL_AREA.getField())) {
            sql.append(",IFNULL(cc.non_residential_area,0) as non_residential_area");
        }
        //27 绿化面积
        if (fields.contains(StatisticalReportEnum.GREEN_AREA.getField())) {
            sql.append(",IFNULL(dd.green_area,0) as green_area");
        }
        //28 公建配套用房面积
        if (fields.contains(StatisticalReportEnum.PUBLIC_AREA.getField())) {
            sql.append(",IFNULL(ee.public_area,0) as public_area");
        }
        //29 住宅户数
        if (fields.contains(StatisticalReportEnum.HOUSEHOLDS.getField())) {
            sql.append(",IFNULL(ff.households,0) as households");
        }
        //30 居住人数
        if (fields.contains(StatisticalReportEnum.NUMBER.getField())) {
            sql.append(",IFNULL(gg.number,0) as number");
        }
        //31 老旧小区数量
        if (fields.contains(StatisticalReportEnum.OLD_DISTRICT.getField())) {
            sql.append(",IFNULL(hh.old_district,0) as old_district");
        }
        //32 智能化管理小区数量
        if (fields.contains(StatisticalReportEnum.INTELLIGENT.getField())) {
            sql.append(",IFNULL(jj.intelligent,0) as intelligent");
        }
        //33 保障住房数量
        if (fields.contains(StatisticalReportEnum.INDEMNIFICATORY.getField())) {
            sql.append(",IFNULL(kk.indemnificatory,0) as indemnificatory");
        }
        //34 业委会数量
        if (fields.contains(StatisticalReportEnum.PROPRIETOR.getField())) {
            sql.append(",IFNULL(ll.proprietor,0) as proprietor");
        }
        //35 优良信息数量
        if (fields.contains(StatisticalReportEnum.GOOD_INFO.getField())) {
            sql.append(",IFNULL(mm.good_info,0) as good_info");
        }
        //36 负面信息数量
        if (fields.contains(StatisticalReportEnum.BAD_INFO.getField())) {
            sql.append(",IFNULL(nn.bad_info,0) as bad_info");
        }
        //37 项目经理培训数量
        if (fields.contains(StatisticalReportEnum.MANAGE_TRAIN.getField())) {
            sql.append(",IFNULL(pp.manager_train,0) as manager_train");
        }
        //38 其他培训数量
        if (fields.contains(StatisticalReportEnum.OTHER_TRAIN.getField())) {
            sql.append(",IFNULL(qq.other_train,0) as other_train");
        }
        //39 市级及以上物业主管部门培训数量
        if (fields.contains(StatisticalReportEnum.CITY_DEPARTMENT.getField())) {
            sql.append(",IFNULL(rr.city_department,0) as city_department");
        }
        //40 区（市）物业主管部门培训数量
        if (fields.contains(StatisticalReportEnum.DISTRICT_DEPARTMENT.getField())) {
            sql.append(",IFNULL(ss.district_department,0) as district_department");
        }
        //41 国家、省、市物业协会培训数量
        if (fields.contains(StatisticalReportEnum.ASSOCIATION.getField())) {
            sql.append(",IFNULL(tt.association,0) as association");
        }
        //42 培训总数量
        if (fields.contains(StatisticalReportEnum.ALL_TRAIN.getField())) {
            sql.append(",IFNULL(vv.all_train,0) as all_train");
        }
        sql.append(" FROM district_street ds");
        //1查询企业数量
        if (fields.contains(StatisticalReportEnum.CORP_NUM.getField())) {
            getCorpNum(sql, map, type);
        }
        //2项目数量
        if (fields.contains(StatisticalReportEnum.PROJECT_NUM.getField())) {
            getProjectNum(sql, map, type);
        }
        //3通过三体系认证数量
        if (fields.contains(StatisticalReportEnum.THREE_SYSTEM.getField())) {
            getThreeSystemNum(sql, map, type, Common.ONE, "d", StatisticalReportEnum.THREE_SYSTEM.getField());
        }
        //4未通过三体系认证数量
        if (fields.contains(StatisticalReportEnum.NO_THREE_SYSTEM.getField())) {
            getThreeSystemNum(sql, map, type, Common.ZERO, "e", StatisticalReportEnum.NO_THREE_SYSTEM.getField());
        }
        //5成立党支部数量
        if (fields.contains(StatisticalReportEnum.ORGANIZATION.getField())) {
            getOrganizationNum(sql, map, type, Common.ONE, "f", StatisticalReportEnum.ORGANIZATION.getField());
        }
        //6未成立党支部数量
        if (fields.contains(StatisticalReportEnum.NO_ORGANIZATION.getField())) {
            getOrganizationNum(sql, map, type, Common.ZERO, "g", StatisticalReportEnum.NO_ORGANIZATION.getField());
        }
        //7主营收入
        if (fields.contains(StatisticalReportEnum.MAIN_INCOME.getField())) {
            getIncome(sql, map, type, StatisticalReportEnum.MAIN_INCOME.getField(), "h");
        }
        //8公共收益
        if (fields.contains(StatisticalReportEnum.PUBLIC_INCOME.getField())) {
            getIncome(sql, map, type, StatisticalReportEnum.PUBLIC_INCOME.getField(), "j");
        }
        //9利润
        if (fields.contains(StatisticalReportEnum.PROFIT.getField())) {
            getIncome(sql, map, type, StatisticalReportEnum.PROFIT.getField(), "k");
        }
        //10其他收入
        if (fields.contains(StatisticalReportEnum.OTHER_INCOME.getField())) {
            getIncome(sql, map, type, StatisticalReportEnum.OTHER_INCOME.getField(), "l");
        }
        //11企业总人数
        if (fields.contains(StatisticalReportEnum.CORP_ALL_PERSON_NUM.getField())) {
            getCorpPersonNum(sql, map, type, StatisticalReportEnum.CORP_ALL_PERSON_NUM.getField(), "m");
        }
        //12企业交五险一金人数
        if (fields.contains(StatisticalReportEnum.CORP_INSURANCE.getField())) {
            getCorpPersonNum(sql, map, type, StatisticalReportEnum.CORP_INSURANCE.getField(), "n");
        }
        //13企业持有相关中等从业资格证书的人数
        if (fields.contains(StatisticalReportEnum.CORP_CERTIFICATE.getField())) {
            getCorpPersonNum(sql, map, type, StatisticalReportEnum.CORP_CERTIFICATE.getField(), "p");
        }
        //14项目总人数
        if (fields.contains(StatisticalReportEnum.PROJECT_ALL_PERSON_NUM.getField())) {
            getProjectPersonNum(sql, map, type, StatisticalReportEnum.PROJECT_ALL_PERSON_NUM.getField(), "q");
        }
        //15项目交五险一金人数
        if (fields.contains(StatisticalReportEnum.PROJECT_INSURANCE.getField())) {
            getProjectPersonNum(sql, map, type, StatisticalReportEnum.PROJECT_INSURANCE.getField(), "r");
        }
        //16项目持有相关中等从业资格证书的人数
        if (fields.contains(StatisticalReportEnum.PROJECT_CERTIFICATE.getField())) {
            getProjectPersonNum(sql, map, type, StatisticalReportEnum.PROJECT_CERTIFICATE.getField(), "s");
        }
        //17 D级
        if (fields.contains(StatisticalReportEnum.LEVEL_D.getField())) {
            getLevelNum(sql, map, type, StatisticalReportEnum.LEVEL_D.getField(), "t", LevelEnum.D.getLevel());
        }
        //18 A级
        if (fields.contains(StatisticalReportEnum.LEVEL_A.getField())) {
            getLevelNum(sql, map, type, StatisticalReportEnum.LEVEL_A.getField(), "u", LevelEnum.A.getLevel());
        }
        //19 AA级
        if (fields.contains(StatisticalReportEnum.LEVEL_AA.getField())) {
            getLevelNum(sql, map, type, StatisticalReportEnum.LEVEL_AA.getField(), "v", LevelEnum.AA.getLevel());
        }
        //20 AAA级
        if (fields.contains(StatisticalReportEnum.LEVEL_AAA.getField())) {
            getLevelNum(sql, map, type, StatisticalReportEnum.LEVEL_AAA.getField(), "w", LevelEnum.AAA.getLevel());
        }
        //21 黑名单数量
        if (fields.contains(StatisticalReportEnum.BLACK_LIST.getField())) {
            getBlackList(sql, map, type, StatisticalReportEnum.BLACK_LIST.getField(), "x");
        }
        //22 项目总占地面积
        if (fields.contains(StatisticalReportEnum.PROJECT_ALL_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.PROJECT_ALL_AREA.getField(), "y", "project_area");
        }
        //23 地上建筑面积
        if (fields.contains(StatisticalReportEnum.GROUND_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.GROUND_AREA.getField(), "z", "building_land");
        }
        //24 地下建筑面积
        if (fields.contains(StatisticalReportEnum.UNDERGROUND_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.UNDERGROUND_AREA.getField(), "aa", "green_area");
        }
        //25 住宅建筑面积
        if (fields.contains(StatisticalReportEnum.RESIDENTIAL_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.RESIDENTIAL_AREA.getField(), "bb", "public_facilities_area");
        }
        //26 非住宅建筑面积
        if (fields.contains(StatisticalReportEnum.NON_RESIDENTIAL_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.NON_RESIDENTIAL_AREA.getField(), "cc", "other_area");
        }
        //27 绿化面积
        if (fields.contains(StatisticalReportEnum.GREEN_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.GREEN_AREA.getField(), "dd", "municipal");
        }
        //28 公建配套用房面积
        if (fields.contains(StatisticalReportEnum.PUBLIC_AREA.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.PUBLIC_AREA.getField(), "ee", "all_area");
        }
        //29 住宅户数
        if (fields.contains(StatisticalReportEnum.HOUSEHOLDS.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.HOUSEHOLDS.getField(), "ff", "housing_quantity");
        }
        //30 居住人数
        if (fields.contains(StatisticalReportEnum.NUMBER.getField())) {
            getProjectArea(sql, map, type, StatisticalReportEnum.NUMBER.getField(), "gg", "number_residents");
        }
        //31 老旧小区数量
        if (fields.contains(StatisticalReportEnum.OLD_DISTRICT.getField())) {
            getHouseNum(sql, map, type, StatisticalReportEnum.OLD_DISTRICT.getField(), "hh");
        }
        //32 智能化管理小区数量
        if (fields.contains(StatisticalReportEnum.INTELLIGENT.getField())) {
            getHouseNum(sql, map, type, StatisticalReportEnum.INTELLIGENT.getField(), "jj");
        }
        //33 保障住房数量
        if (fields.contains(StatisticalReportEnum.INDEMNIFICATORY.getField())) {
            getHouseNum(sql, map, type, StatisticalReportEnum.INDEMNIFICATORY.getField(), "kk");
        }
        //34 业委会数量
        if (fields.contains(StatisticalReportEnum.PROPRIETOR.getField())) {
            getProprietor(sql, map, type, StatisticalReportEnum.PROPRIETOR.getField(), "ll");
        }
        //35 优良信息数量
        if (fields.contains(StatisticalReportEnum.GOOD_INFO.getField())) {
            getInfoNum(sql,map,type,StatisticalReportEnum.GOOD_INFO.getField(),"mm", InfoTypeEnum.EXCELLENT.getCode());
        }
        //36 负面信息数量
        if (fields.contains(StatisticalReportEnum.BAD_INFO.getField())) {
            getInfoNum(sql,map,type,StatisticalReportEnum.BAD_INFO.getField(),"nn", InfoTypeEnum.NEGATIVE.getCode());
        }
        //37 项目经理培训数量
        if (fields.contains(StatisticalReportEnum.MANAGE_TRAIN.getField())) {
            getTrainNum(sql,map,type,StatisticalReportEnum.MANAGE_TRAIN.getField(),"pp","subject_type",Common.ONE);
        }
        //38 其他培训数量
        if (fields.contains(StatisticalReportEnum.OTHER_TRAIN.getField())) {
            getTrainNum(sql,map,type,StatisticalReportEnum.OTHER_TRAIN.getField(),"qq","subject_type",Common.TWO);
        }
        //39 市级及以上物业主管部门培训数量
        if (fields.contains(StatisticalReportEnum.CITY_DEPARTMENT.getField())) {
            getTrainNum(sql,map,type,StatisticalReportEnum.CITY_DEPARTMENT.getField(),"rr","department",Common.ONE);
        }
        //40 区（市）物业主管部门培训数量
        if (fields.contains(StatisticalReportEnum.DISTRICT_DEPARTMENT.getField())) {
            getTrainNum(sql,map,type,StatisticalReportEnum.DISTRICT_DEPARTMENT.getField(),"ss","department",Common.TWO);
        }
        //41 国家、省、市物业协会培训数量
        if (fields.contains(StatisticalReportEnum.ASSOCIATION.getField())) {
            getTrainNum(sql,map,type,StatisticalReportEnum.ASSOCIATION.getField(),"tt","department", String.valueOf(Common.THREE));
        }
        //42 培训总数量
        if (fields.contains(StatisticalReportEnum.ALL_TRAIN.getField())) {
            getTrainNum(sql,map,type,StatisticalReportEnum.ALL_TRAIN.getField(),"vv",null, null);
        }
        sql.append(" WHERE ");
        if (Common.THREE.equals(roleId)) {
            sql.append(" ds.parent_id = 0");
        } else {
            sql.append(" ds.parent_id <> 0");
        }
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and ds.id in (").append(area).append(")");
        }
        return sql.toString();
    }

    /**
     * 1查询企业数量
     *
     * @param sql sql
     * @param map map
     */
    private void getCorpNum(StringBuffer sql, HashMap<String, Object> map, String type) {
        sql.append(" LEFT JOIN ( SELECT ").append(type).append(", COUNT(*) AS corpNum ");
        sql.append(" FROM company_base_info c WHERE c.state IN (2, 9)");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") b ON b.").append(type).append(" = ds.id");
    }

    /**
     * 查询项目数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getProjectNum(StringBuffer sql, HashMap<String, Object> map, String type) {
        sql.append(" LEFT JOIN (select ").append(type).append(",COUNT(*) AS projectNum");
        sql.append(" FROM company_project c WHERE c.state in (2,9)");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") c ON c.").append(type).append(" = ds.id");
    }

    /**
     * 查询三体系认证数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getThreeSystemNum(StringBuffer sql, HashMap<String, Object> map, String type, String value, String key, String param) {
        sql.append(" LEFT JOIN (select ").append(type).append(", COUNT(*) as ").append(param).append(" FROM company_base_info c ");
        sql.append(" WHERE c.state in (2,9) and c.three_system=").append(value);
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询党支部数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getOrganizationNum(StringBuffer sql, HashMap<String, Object> map, String type, String value, String key, String param) {
        sql.append(" LEFT JOIN (select ").append(type).append(", COUNT(*) as ").append(param).append(" FROM company_base_info c ");
        sql.append(" WHERE c.state in (2,9) and c.organization=").append(value);
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询主营收入
     *
     * @param map 参数
     * @return SQL
     */
    public void getIncome(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key) {
        sql.append(" LEFT JOIN (SELECT ").append(type).append(",SUM(").append(param).append(") AS ").append(param);
        sql.append(" FROM company_base_info c WHERE c.state IN (2, 9)");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询企业人数
     *
     * @param map 参数
     * @return SQL
     */
    public void getCorpPersonNum(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_person p LEFT JOIN company_base_info c ON p.company_id=c.id WHERE p.person_id=0 and c.state in(2,9) ");
        if (StatisticalReportEnum.CORP_INSURANCE.getField().equals(param)) {
            sql.append(" and p.insurance=1");
        } else if (StatisticalReportEnum.CORP_CERTIFICATE.getField().equals(param)) {
            sql.append(" and p.certificate=1");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "p");
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询项目人数
     *
     * @param map 参数
     * @return SQL
     */
    public void getProjectPersonNum(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_person p LEFT JOIN company_project c ON p.project_id=c.id WHERE p.person_id<>0 and c.state=2 ");
        if (StatisticalReportEnum.PROJECT_INSURANCE.getField().equals(param)) {
            sql.append(" and p.insurance=1");
        } else if (StatisticalReportEnum.PROJECT_CERTIFICATE.getField().equals(param)) {
            sql.append(" and p.certificate=1");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "p");
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询各个等级的数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getLevelNum(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key, String parameter) {
        ExportExcel exportExcel = (ExportExcel) map.get("exportExcel");
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM score_record s LEFT JOIN company_base_info c on s.poguid=c.id");
        sql.append(" WHERE s.dj='").append(parameter).append("' AND s.`year`=").append(exportExcel.getYear()).append(" and c.state in(2,9) ");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "s");
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询黑名单数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getBlackList(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_blacklist b LEFT JOIN company_base_info c on b.company_id = c.id WHERE b.state=1");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "b");
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询项目各种面积
     *
     * @param map 参数
     * @return SQL
     */
    public void getProjectArea(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key, String parameter) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",SUM(").append(parameter).append(") AS ").append(param);
        sql.append(" FROM company_project c WHERE c.state=2 ");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询老旧小区，智能化管理小区，保障住房数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getHouseNum(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_project c WHERE c.state=2 ");
        if (StatisticalReportEnum.OLD_DISTRICT.getField().equals(param)) {
            sql.append(" and c.is_old=1");
        } else if (StatisticalReportEnum.INTELLIGENT.getField().equals(param)) {
            sql.append(" and c.is_intelligent=1");
        } else if (StatisticalReportEnum.INDEMNIFICATORY.getField().equals(param)) {
            sql.append(" and c.is_indemnificatory=1");
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, StatisticalReportDaoProvider.C);
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询业委会数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getProprietor(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_proprietor p LEFT JOIN company_project c ON p.project_id=c.project_id WHERE c.state=2 ");
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "p");
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询优良,负面信息数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getInfoNum(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key,Integer infoType) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append("  FROM (SELECT (CASE WHEN d.project_id=0 THEN c. ").append(type).append(" ELSE p.").append(type).append(" end) ").append(" as ").append(type);
        sql.append(" FROM score_detail d ");
        sql.append(" LEFT JOIN company_project p ON d.project_id = p.id");
        sql.append(" LEFT JOIN company_base_info c ON d.corp_id = c.id");
        sql.append(" WHERE d.info_type=").append(infoType).append(" and d.state IN (2, 4, 5, 6, 7)");
        StatisticalReportDaoProvider.commonInfoSql(sql, map);
        sql.append(" )c GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }

    /**
     * 查询教育数量
     *
     * @param map 参数
     * @return SQL
     */
    public void getTrainNum(StringBuffer sql, HashMap<String, Object> map, String type, String param, String key,String parameter,String value) {
        sql.append(" LEFT JOIN (SELECT c.").append(type).append(",COUNT(*) AS ").append(param);
        sql.append(" FROM company_train t LEFT JOIN company_base_info c ON c.id=t.corp_id");
        sql.append(" WHERE c.state in(2,9)");
        if (StringUtils.isNotBlank(parameter)) {
            sql.append(" and t.").append(parameter).append("=").append(value);
        }
        StatisticalReportDaoProvider.commonSql(sql, map, StatisticalReportDaoProvider.C, "t");
        sql.append(" GROUP BY c.").append(type).append(") ").append(key).append(" ON ").append(key).append(".").append(type).append(" = ds.id");
    }
}
